package net.zoneland.knowledge.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.UpdateProvider;
import org.apache.ibatis.type.JdbcType;

import net.zoneland.knowledge.model.QaRecordsV2;
import net.zoneland.knowledge.model.QaRecordsV2Example;
import net.zoneland.knowledge.model.StatisticsBo;

@Mapper
public interface QaRecordsV2Mapper {
    @SelectProvider(type=QaRecordsV2SqlProvider.class, method="countByExample")
    long countByExample(QaRecordsV2Example example);

    @Delete({
        "delete from AITK_QA_RECORDS_V2",
        "where ID = #{id,jdbcType=VARCHAR}"
    })
    int deleteByPrimaryKey(String id);

    @Insert({
        "insert into AITK_QA_RECORDS_V2 (ID, CATEGORY, ",
        "QA_TYPE, LEVEL1_TITLE, ",
        "LEVEL2_TITLE, LEVEL3_TITLE, ",
        "QUERY_TEXT, QUESTYLE, ",
        "REQID, REQNAME, ",
        "CMPDN, DEPTDN, CREATETIME, ",
        "PRAISE, FINALANSWER, ",
        "ANSCODE, BANKID, ",
        "SESSIONID, ANSCONTENT, VISIT_TYPE, CLICK_SEARCH,HAS_ANSWER)",
        "values (#{id,jdbcType=VARCHAR}, #{category,jdbcType=VARCHAR}, ",
        "#{qaType,jdbcType=VARCHAR}, #{level1Title,jdbcType=VARCHAR}, ",
        "#{level2Title,jdbcType=VARCHAR}, #{level3Title,jdbcType=VARCHAR}, ",
        "#{queryText,jdbcType=VARCHAR}, #{queStyle,jdbcType=VARCHAR}, ",
        "#{reqId,jdbcType=VARCHAR}, #{reqName,jdbcType=VARCHAR}, ",
        "#{cmpDn,jdbcType=VARCHAR}, #{deptDn,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, ",
        "#{praise,jdbcType=DECIMAL}, #{finalAnswer,jdbcType=DECIMAL}, ",
        "#{ansCode,jdbcType=DECIMAL}, #{bankId,jdbcType=VARCHAR}, ",
        "#{sessionId,jdbcType=VARCHAR}, #{ansContent,jdbcType=CLOB},",
        "#{visitType,jdbcType=VARCHAR}, #{clickSearch,jdbcType=DECIMAL}, #{hasAnswer,jdbcType=BOOLEAN})"
    })
    int insert(QaRecordsV2 record);

    @InsertProvider(type=QaRecordsV2SqlProvider.class, method="insertSelective")
    int insertSelective(QaRecordsV2 record);

    @SelectProvider(type=QaRecordsV2SqlProvider.class, method="selectByExampleWithBLOBs")
    @Results({
        @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR, id=true),
        @Result(column="CATEGORY", property="category", jdbcType=JdbcType.VARCHAR),
        @Result(column="QA_TYPE", property="qaType", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL1_TITLE", property="level1Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL2_TITLE", property="level2Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL3_TITLE", property="level3Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="QUERY_TEXT", property="queryText", jdbcType=JdbcType.VARCHAR),
        @Result(column="QUESTYLE", property="queStyle", jdbcType=JdbcType.VARCHAR),
        @Result(column="REQID", property="reqId", jdbcType=JdbcType.VARCHAR),
        @Result(column="REQNAME", property="reqName", jdbcType=JdbcType.VARCHAR),
        @Result(column="CMPDN", property="cmpDn", jdbcType=JdbcType.VARCHAR),
        @Result(column="DEPTDN", property="deptDn", jdbcType=JdbcType.VARCHAR),
        @Result(column="CREATETIME", property="createTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="PRAISE", property="praise", jdbcType=JdbcType.DECIMAL),
        @Result(column="FINALANSWER", property="finalAnswer", jdbcType=JdbcType.DECIMAL),
        @Result(column="CLICK_SEARCH", property="clickSearch", jdbcType=JdbcType.DECIMAL),
        @Result(column="ANSCODE", property="ansCode", jdbcType=JdbcType.DECIMAL),
        @Result(column="BANKID", property="bankId", jdbcType=JdbcType.VARCHAR),
        @Result(column="SESSIONID", property="sessionId", jdbcType=JdbcType.VARCHAR),
        @Result(column="VISIT_TYPE", property="visitType", jdbcType=JdbcType.VARCHAR),
        @Result(column="ANSCONTENT", property="ansContent", jdbcType=JdbcType.CLOB)
    })
    List<QaRecordsV2> selectByExampleWithBLOBs(QaRecordsV2Example example);

    @SelectProvider(type=QaRecordsV2SqlProvider.class, method="selectByExample")
    @Results({
        @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR, id=true),
        @Result(column="CATEGORY", property="category", jdbcType=JdbcType.VARCHAR),
        @Result(column="QA_TYPE", property="qaType", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL1_TITLE", property="level1Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL2_TITLE", property="level2Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL3_TITLE", property="level3Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="QUERY_TEXT", property="queryText", jdbcType=JdbcType.VARCHAR),
        @Result(column="QUESTYLE", property="queStyle", jdbcType=JdbcType.VARCHAR),
        @Result(column="REQID", property="reqId", jdbcType=JdbcType.VARCHAR),
        @Result(column="REQNAME", property="reqName", jdbcType=JdbcType.VARCHAR),
        @Result(column="CMPDN", property="cmpDn", jdbcType=JdbcType.VARCHAR),
        @Result(column="DEPTDN", property="deptDn", jdbcType=JdbcType.VARCHAR),
        @Result(column="CREATETIME", property="createTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="PRAISE", property="praise", jdbcType=JdbcType.DECIMAL),
        @Result(column="FINALANSWER", property="finalAnswer", jdbcType=JdbcType.DECIMAL),
        @Result(column="CLICK_SEARCH", property="clickSearch", jdbcType=JdbcType.DECIMAL),
        @Result(column="ANSCODE", property="ansCode", jdbcType=JdbcType.DECIMAL),
        @Result(column="BANKID", property="bankId", jdbcType=JdbcType.VARCHAR),
        @Result(column="VISIT_TYPE", property="visitType", jdbcType=JdbcType.VARCHAR),
        @Result(column="SESSIONID", property="sessionId", jdbcType=JdbcType.VARCHAR)
    })
    List<QaRecordsV2> selectByExample(QaRecordsV2Example example);

    @Select({
        "select",
        "ID, CATEGORY, QA_TYPE, LEVEL1_TITLE, LEVEL2_TITLE, LEVEL3_TITLE, QUERY_TEXT, ",
        "QUESTYLE, REQID, REQNAME, CMPDN, DEPTDN, CREATETIME, PRAISE, FINALANSWER, CLICK_SEARCH, ANSCODE, ",
        "BANKID, SESSIONID, ANSCONTENT, VISIT_TYPE",
        "from AITK_QA_RECORDS_V2",
        "where ID = #{id,jdbcType=VARCHAR}"
    })
    @Results({
        @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR, id=true),
        @Result(column="CATEGORY", property="category", jdbcType=JdbcType.VARCHAR),
        @Result(column="QA_TYPE", property="qaType", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL1_TITLE", property="level1Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL2_TITLE", property="level2Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="LEVEL3_TITLE", property="level3Title", jdbcType=JdbcType.VARCHAR),
        @Result(column="QUERY_TEXT", property="queryText", jdbcType=JdbcType.VARCHAR),
        @Result(column="QUESTYLE", property="queStyle", jdbcType=JdbcType.VARCHAR),
        @Result(column="REQID", property="reqId", jdbcType=JdbcType.VARCHAR),
        @Result(column="REQNAME", property="reqName", jdbcType=JdbcType.VARCHAR),
        @Result(column="CMPDN", property="cmpDn", jdbcType=JdbcType.VARCHAR),
        @Result(column="DEPTDN", property="deptDn", jdbcType=JdbcType.VARCHAR),
        @Result(column="CREATETIME", property="createTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="PRAISE", property="praise", jdbcType=JdbcType.DECIMAL),
        @Result(column="FINALANSWER", property="finalAnswer", jdbcType=JdbcType.DECIMAL),
        @Result(column="CLICK_SEARCH", property="clickSearch", jdbcType=JdbcType.DECIMAL),
        @Result(column="ANSCODE", property="ansCode", jdbcType=JdbcType.DECIMAL),
        @Result(column="BANKID", property="bankId", jdbcType=JdbcType.VARCHAR),
        @Result(column="SESSIONID", property="sessionId", jdbcType=JdbcType.VARCHAR),
        @Result(column="VISIT_TYPE", property="visitType", jdbcType=JdbcType.VARCHAR),
        @Result(column="ANSCONTENT", property="ansContent", jdbcType=JdbcType.CLOB)
    })
    QaRecordsV2 selectByPrimaryKey(String id);

    @UpdateProvider(type=QaRecordsV2SqlProvider.class, method="updateByExampleSelective")
    int updateByExampleSelective(@Param("record") QaRecordsV2 record, @Param("example") QaRecordsV2Example example);

    @UpdateProvider(type=QaRecordsV2SqlProvider.class, method="updateByExampleWithBLOBs")
    int updateByExampleWithBLOBs(@Param("record") QaRecordsV2 record, @Param("example") QaRecordsV2Example example);

    @UpdateProvider(type=QaRecordsV2SqlProvider.class, method="updateByExample")
    int updateByExample(@Param("record") QaRecordsV2 record, @Param("example") QaRecordsV2Example example);

    @UpdateProvider(type=QaRecordsV2SqlProvider.class, method="updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(QaRecordsV2 record);

    @Update({
        "update AITK_QA_RECORDS_V2",
        "set CATEGORY = #{category,jdbcType=VARCHAR},",
          "QA_TYPE = #{qaType,jdbcType=VARCHAR},",
          "LEVEL1_TITLE = #{level1Title,jdbcType=VARCHAR},",
          "LEVEL2_TITLE = #{level2Title,jdbcType=VARCHAR},",
          "LEVEL3_TITLE = #{level3Title,jdbcType=VARCHAR},",
          "QUERY_TEXT = #{queryText,jdbcType=VARCHAR},",
          "QUESTYLE = #{queStyle,jdbcType=VARCHAR},",
          "REQID = #{reqId,jdbcType=VARCHAR},",
          "REQNAME = #{reqName,jdbcType=VARCHAR},",
          "CMPDN = #{cmpDn,jdbcType=VARCHAR},",
          "DEPTDN = #{deptDn,jdbcType=VARCHAR},",
          "CREATETIME = #{createTime,jdbcType=TIMESTAMP},",
          "PRAISE = #{praise,jdbcType=DECIMAL},",
          "FINALANSWER = #{finalAnswer,jdbcType=DECIMAL},",
          "CLICK_SEARCH = #{clickSearch,jdbcType=DECIMAL},",
          "ANSCODE = #{ansCode,jdbcType=DECIMAL},",
          "BANKID = #{bankId,jdbcType=VARCHAR},",
          "SESSIONID = #{sessionId,jdbcType=VARCHAR},",
          "VISIT_TYPE = #{visitType,jdbcType=VARCHAR},",
          "ANSCONTENT = #{ansContent,jdbcType=CLOB}",
        "where ID = #{id,jdbcType=VARCHAR}"
    })
    int updateByPrimaryKeyWithBLOBs(QaRecordsV2 record);

    @Update({
        "update AITK_QA_RECORDS_V2",
        "set CATEGORY = #{category,jdbcType=VARCHAR},",
          "QA_TYPE = #{qaType,jdbcType=VARCHAR},",
          "LEVEL1_TITLE = #{level1Title,jdbcType=VARCHAR},",
          "LEVEL2_TITLE = #{level2Title,jdbcType=VARCHAR},",
          "LEVEL3_TITLE = #{level3Title,jdbcType=VARCHAR},",
          "QUERY_TEXT = #{queryText,jdbcType=VARCHAR},",
          "QUESTYLE = #{queStyle,jdbcType=VARCHAR},",
          "REQID = #{reqId,jdbcType=VARCHAR},",
          "REQNAME = #{reqName,jdbcType=VARCHAR},",
          "CMPDN = #{cmpDn,jdbcType=VARCHAR},",
          "DEPTDN = #{deptDn,jdbcType=VARCHAR},",
          "CREATETIME = #{createTime,jdbcType=TIMESTAMP},",
          "PRAISE = #{praise,jdbcType=DECIMAL},",
          "FINALANSWER = #{finalAnswer,jdbcType=DECIMAL},",
          "CLICK_SEARCH = #{clickSearch,jdbcType=DECIMAL},",
          "ANSCODE = #{ansCode,jdbcType=DECIMAL},",
          "BANKID = #{bankId,jdbcType=VARCHAR},",
          "VISIT_TYPE = #{visitType,jdbcType=VARCHAR},",
          "SESSIONID = #{sessionId,jdbcType=VARCHAR}",
        "where ID = #{id,jdbcType=VARCHAR}"
    })
    int updateByPrimaryKey(QaRecordsV2 record);

    @Select({"SELECT query_text as queName,id FROM ( SELECT query_text,COUNT (*) AS id FROM AITK_QA_RECORDS_V2 WHERE questyle='1'\n" +
            "                and finalanswer=1 and ansCode=1 and cmpDn= #{companyDn} and trim(translate(query_text, '0123456789', ' ')) is not null \n" +
            "                and category like #{category} AND bankid not like 'RHC-RHC_PERSON%' \n" +
            "                 and createtime >= trunc(add_months(sysdate,-1)) GROUP BY query_text ORDER BY COUNT (*) DESC)  WHERE ROWNUM <= #{limit}"})
    @Results({
            @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR, id=true),
            @Result(column="queName", property="queName", jdbcType=JdbcType.VARCHAR),
    })
    List<StatisticsBo>  queryHotQuestion(@Param("companyDn") String companyDn, @Param("category") String category, @Param("limit") int limit);


    @Select({"SELECT query_text as queName,id FROM ( SELECT query_text,COUNT (*) AS id FROM AITK_QA_RECORDS_V2 WHERE questyle='1'\n" +
            "                and finalanswer=0 and ansCode=1 and cmpDn= #{companyDn} and trim(translate(query_text, '0123456789', ' ')) is not null \n" +
            "                 and createtime >= trunc(add_months(sysdate,-1)) AND bankid not like 'RHC-RHC_PERSON%' GROUP BY query_text ORDER BY COUNT (*) DESC)  WHERE ROWNUM <= #{limit}"})
    @Results({
            @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR, id=true),
            @Result(column="queName", property="queName", jdbcType=JdbcType.VARCHAR),
    })
    List<StatisticsBo>  querySearchQuestion(@Param("companyDn") String companyDn,  @Param("limit") int limit);

    @Select({"SELECT category,level1_title,query_text,visit_type, createtime from (" +
            "SELECT category,level1_title,query_text,visit_type, createtime,ROWNUM as num from AITK_QA_RECORDS_V2 " +
            "WHERE reqid = #{userId} and createTime < to_timestamp(#{time},'yyyy-mm-dd hh24:mi:ss') order by createtime desc) where ROWNUM <= 10 "})
    @Results({
            @Result(column="category", property="category", jdbcType=JdbcType.VARCHAR),
            @Result(column="level1_title", property="level1Title", jdbcType=JdbcType.VARCHAR),
            @Result(column="query_text", property="queryText", jdbcType=JdbcType.VARCHAR),
            @Result(column="visit_type", property="visitType", jdbcType=JdbcType.VARCHAR),
            @Result(column="CREATETIME", property="createTime", jdbcType=JdbcType.TIMESTAMP),
    })
    List<QaRecordsV2>  queryFootprint(@Param("userId") String userId, @Param("time") String time);

}
